package br.com.skytef.dao;

public class SqlTicket {

	/*******************************************************************************************
	 * INSERT
	 ********************************************************************************************/
	
	public static String getQryInsertTicket() {
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO ticket ");
		sql.append("            (idempresa, ");
		sql.append("             idtarifa, ");
		sql.append("             idOperador, ");
		sql.append("             datacriacao, ");
		sql.append("             prisma, ");
		sql.append("             veiculoplace, ");
		sql.append("             veiculocor, ");
		sql.append("             veiculotipo, ");
		sql.append("             veiculomarca, ");
		sql.append("             veiculomdelo) ");
		sql.append("VALUES      (?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ");

		return sql.toString();
	}

	public static String getQryInsertTicketMov() {
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO ticket_movimentacao ");
		sql.append("            (ticket_idticket, ");
		sql.append("             idoperador, ");
		sql.append("             tipoMov, ");
		sql.append("             datahoracriacao, ");
		sql.append("             datahoramov, ");
		sql.append("             serialpos) ");
		sql.append("VALUES      (?, ?, ?, ?, ?, ?); ");
		return sql.toString();
	}

	public static String getQryInsertTicketBelongings() {
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO ticket_pertences ");
		sql.append("            (idticket,  ");
		sql.append("             descricao)  ");
		sql.append("VALUES      (?, ?);  ");

		return sql.toString();
	}

	public static String getQryInsertTicketBreakdown() {
		StringBuffer sql = new StringBuffer();

		sql.append("INSERT INTO ticket_avarias  ");
		sql.append("            (idticket,  ");
		sql.append("             avaria)  ");
		sql.append("VALUES      (?, ?); ");

		return sql.toString();
	}

	/*******************************************************************************************
	 * SELECT
	 ********************************************************************************************/

	public static String getQrySelectTicket() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT idticket, ");
		sql.append("       idempresa, ");
		sql.append("       idOperador, ");
		sql.append("       idtarifa, ");
		sql.append("       dataCriacao, ");
		sql.append("       prisma, ");
		sql.append("       veiculoplace, ");
		sql.append("       veiculocor, ");
		sql.append("       veiculotipo, ");
		sql.append("       veiculomarca, ");
		sql.append("       veiculomdelo ");
		sql.append("FROM   ticket ");
		sql.append("WHERE  idticket = ?; ");

		return sql.toString();
	}

	public static String getQrySelectTicketMov() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT ticket_idticket,");
		sql.append("       idoperador, ");
		sql.append("       tipomov, ");
		sql.append("       datahoracriacao, ");
		sql.append("       datahoramov, ");
		sql.append("       serialpos ");
		sql.append("FROM   ticket_movimentacao ");
		sql.append("WHERE ticket_idticket = ? AND ");
		sql.append("       tipomov = ?; ");
		return sql.toString();
	}

	public static String getQrySelectTicketBreakdown() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT ticket_avarias.idAvarias, ");
		sql.append("       ticket_avarias.idticket, ");
		sql.append("       ticket_avarias.avaria ");
		sql.append("FROM   ticket_avarias ");
		sql.append("WHERE idticket = ?;");

		return sql.toString();
	}

	public static String getQrySelectTicketBelongings() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT ticket_pertences.idpertences, ");
		sql.append("       ticket_pertences.idticket, ");
		sql.append("       ticket_pertences.descricao ");
		sql.append("FROM   ticket_pertences ");
		sql.append("WHERE idticket = ?;");

		return sql.toString();
	}

	public static String getQrySelectParkedByPrism() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT * "); 
		sql.append("FROM   ticket "); 
		sql.append("WHERE  prisma = ? "); 
		sql.append("       AND idticket NOT IN (SELECT idticket "); 
		sql.append("                            FROM   ticket tk "); 
		sql.append("                                   INNER JOIN ticket_movimentacao mv "); 
		sql.append("                                           ON tk.idticket = mv.ticket_idticket "); 
		sql.append("                            WHERE  prisma = ? "); 
		sql.append("                                   AND tipomov = 'Saida'); "); 

		return sql.toString();
	}
	
	
	public static String getQrySelectTicketEmp() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT tk.idticket, ");
		sql.append("       tk.idempresa, ");
		sql.append("       tk.idOperador, ");
		sql.append("       tk.idtarifa, ");
		sql.append("       tk.dataCriacao, ");
		sql.append("       tk.prisma, ");
		sql.append("       tk.veiculoplace, ");
		sql.append("       tk.veiculocor, ");
		sql.append("       tk.veiculotipo, ");
		sql.append("       tk.veiculomarca, ");
		sql.append("       tk.veiculomdelo, ");
		sql.append("       es.razao_social, ");
		sql.append("       op.nome ");
		sql.append("FROM   ticket  tk");
		sql.append("                                   INNER JOIN estabelecimento es "); 
		sql.append("                                           ON tk.idEmpresa = es.idEstabelecimento ");
		sql.append("                                   INNER JOIN operador op "); 
		sql.append("                                           ON tk.idOperador = op.idOperador "); 
 
		
		
		return sql.toString();
	}
	
	
	public static String getQrySelectOneTicketEmp() {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT tk.idticket, ");
		sql.append("       tk.idempresa, ");
		sql.append("       tk.idOperador, ");
		sql.append("       tk.idtarifa, ");
		sql.append("       tk.dataCriacao, ");
		sql.append("       tk.prisma, ");
		sql.append("       tk.veiculoplace, ");
		sql.append("       tk.veiculocor, ");
		sql.append("       tk.veiculotipo, ");
		sql.append("       tk.veiculomarca, ");
		sql.append("       tk.veiculomdelo, ");
		sql.append("       es.razao_social, ");
		sql.append("       op.nome ");
		sql.append("FROM   ticket  tk ");
		sql.append("                                   LEFT JOIN estabelecimento es "); 
		sql.append("                                           ON tk.idEmpresa = es.idEstabelecimento ");
		sql.append("                                   LEFT JOIN operador op "); 
		sql.append("                                           ON tk.idOperador = op.idOperador "); 
		sql.append("WHERE  tk.idticket = ? ");
 
		
		
		return sql.toString();
	}
	
	public static String getQrySelectParkedByPlate() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT * ");
		sql.append("FROM   ticket "); 
		sql.append("WHERE  veiculoplace = ? "); 
		sql.append("       AND idticket NOT IN (SELECT idticket "); 
		sql.append("                            FROM   ticket tk "); 
		sql.append("                                   INNER JOIN ticket_movimentacao mv "); 
		sql.append("                                           ON tk.idticket = mv.ticket_idticket "); 
		sql.append("                            WHERE  tk.veiculoplace = ? "); 
		sql.append("                                   AND mv.tipomov = 'Saida'); "); 

		return sql.toString();
	}
	
	public static String getQrySelectParkedAll() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT * ");
		sql.append("FROM   ticket tk  ");
		sql.append("       INNER JOIN ticket_movimentacao mv  ");
		sql.append("               ON tk.idticket = mv.ticket_idticket  ");
		sql.append("WHERE  idticket NOT IN (SELECT idticket  ");
		sql.append("                        FROM   ticket tk  ");
		sql.append("                               INNER JOIN ticket_movimentacao mv  ");
		sql.append("                                       ON tk.idticket = mv.ticket_idticket  ");
		sql.append("                        WHERE  mv.tipomov = 'Saida');  ");

		return sql.toString();
	}
	
	public static String getQrySelectParkedMov() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT * ");
		sql.append("FROM   ticket  ");
		sql.append("WHERE  idticket IN (SELECT idticket  ");
		sql.append("                        FROM   ticket tk  ");
		sql.append("                               INNER JOIN ticket_movimentacao mv  ");
		sql.append("                                       ON tk.idticket = mv.ticket_idticket  ");
		sql.append("                        WHERE  DATE(mv.dataHoraMov) = DATE(?) AND  mv.tipomov = ?);  ");

		return sql.toString();
	}
	
	public static String getQrySelectRelatMovByPeriod() {
		StringBuffer sql = new StringBuffer();

		sql.append("SELECT tkt.idticket, ");
		sql.append("       tkt.idempresa,  ");
		sql.append("       tkt.idoperador,  ");
		sql.append("       tkt.idtarifa,  ");
		sql.append("       tkt.datacriacao,  ");
		sql.append("       tkt.prisma,  ");
		sql.append("       tkt.veiculoplace,  ");
		sql.append("       tkt.veiculocor,  ");
		sql.append("       tkt.veiculotipo,  ");
		sql.append("       tkt.veiculomarca,  ");
		sql.append("       tkt.veiculomdelo,  ");
		sql.append("       mve.tipomov,  ");
		sql.append("       mve.idoperador,  ");
		sql.append("       mve.datahoramov,  ");
		sql.append("       mvs.tipomov,  ");
		sql.append("       mvs.idoperador,  ");
		sql.append("       mvs.datahoramov,  ");
		sql.append("       TIME(mvs.datahoramov-mve.datahoramov) as periodo,  ");
		sql.append("       opt.nome,  ");
		sql.append("       ope.nome,  ");
		sql.append("       ops.nome,  ");
		sql.append("       trf.descricaoTarifa,  ");
		sql.append("       pgt.idpagamento,  ");
		sql.append("       pgt.idticket,  ");
		sql.append("       pgt.ativo,  ");
		sql.append("       pgt.tipoorigem,  ");
		sql.append("       pgt.datacriacao,  ");
		sql.append("       pgt.datadocumento,  ");
		sql.append("       pgt.datavencimento,  ");
		sql.append("       pgt.datapgto,  ");
		sql.append("       pgt.momentopgto,  ");
		sql.append("       pgt.formapgto,  ");
		sql.append("       pgt.valortarifa,  ");
		sql.append("       pgt.multa,  ");
		sql.append("       pgt.juros,  ");
		sql.append("       pgt.descontos,  ");
		sql.append("       pgt.valorpago,  ");
		sql.append("       pgt.obs  ");
		sql.append("FROM   ticket tkt  ");
		sql.append("       LEFT JOIN ticket_movimentacao mvs  ");
		sql.append("              ON tkt.idticket = mvs.ticket_idticket  ");
		sql.append("                 AND mvs.tipomov = 'Saida'  ");
		sql.append("       LEFT JOIN ticket_movimentacao mve  ");
		sql.append("              ON tkt.idticket = mve.ticket_idticket  ");
		sql.append("                 AND mve.tipomov = 'Entrada'  ");
		sql.append("       LEFT JOIN operador opt  ");
		sql.append("              ON tkt.idoperador = opt.idoperador  ");
		sql.append("       LEFT JOIN operador ope  ");
		sql.append("              ON tkt.idoperador = ope.idoperador  ");
		sql.append("       LEFT JOIN operador ops  ");
		sql.append("              ON tkt.idoperador = ops.idoperador  ");
		sql.append("       LEFT JOIN pagamento pgt  ");
		sql.append("              ON tkt.idticket = pgt.idticket  ");
		sql.append("              LEFT JOIN tarifa trf  ");
		sql.append("                     ON tkt.idTarifa = trf.idTarifa ");		
		sql.append("WHERE DATE(mve.datahoramov) >= '2014-12-11' "); 
		sql.append("      AND (DATE(mvs.datahoramov) <= '2014-12-11' "); 
		sql.append("      OR mvs.datahoramov is null); ");


		return sql.toString();
	}

	/*******************************************************************************************
	 * UPDATE
	 ********************************************************************************************/
	public static String getQryUpdateTicket() {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ticket ");
		sql.append("SET    idempresa = ?, ");
		sql.append("       idtarifa = ?, ");
		sql.append("       idOperador = ?, ");
		sql.append("       dataCriacao = ?, ");
		sql.append("       prisma = ?, ");
		sql.append("       veiculoplace = ?, ");
		sql.append("       veiculocor = ?, ");
		sql.append("       veiculotipo = ?, ");
		sql.append("       veiculomarca = ?, ");
		sql.append("       veiculomdelo = ? ");
		sql.append("WHERE  idticket = ? ");

		return sql.toString();
	}

	public static String getQryUpdateTicketMov() {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE ticket_movimentacao ");
		sql.append("SET    idoperador = ?, ");
		sql.append("       tipomov = ?, ");
		sql.append("       datahoracriacao = ?, ");
		sql.append("       datahoramov = ?, ");
		sql.append("       serialpos = ? ");
		sql.append("WHERE  ticket_idticket = ?; ");
		return sql.toString();
	}

	public static String getQryUpdateTicketBreakdown() {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE ticket_avarias ");
		sql.append("SET    idticket_avarias = ?,  ");
		sql.append("       descricao_local = ?  ");
		sql.append("WHERE  idticket_avarias = ?;  ");

		return sql.toString();
	}

	public static String getQryUpdateTicketBelongings() {
		StringBuffer sql = new StringBuffer();

		sql.append("UPDATE ticket_pertences  ");
		sql.append("SET    idticket_pertences = ?,  ");
		sql.append("       idticket = ?,  ");
		sql.append("       descricao = ?  ");
		sql.append("WHERE  idticket_pertences = ?  ");
		sql.append("       AND idticket = ?;  ");

		return sql.toString();
	}

	/*******************************************************************************************
	 * DELETE
	 ********************************************************************************************/
	public static String getQryDeleteTicket() {
		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM ticket ");
		sql.append("WHERE  idticket = ? ");

		return sql.toString();
	}

	public static String getQryDeleteTicketMov() {
		StringBuffer sql = new StringBuffer();
		
		sql.append("DELETE FROM ticket_movimentacao ");
		sql.append("WHERE  ticket_idticket = ?; ");
		
		return sql.toString();
	}

	public static String getQryDeleteTicketBreakdown() {
		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM ticket_avarias  ");
		sql.append("WHERE idticket = ?;  ");

		return sql.toString();
	}

	public static String getQryDeleteTicketBelongings() {
		StringBuffer sql = new StringBuffer();

		sql.append("DELETE FROM ticket_pertences  ");
		sql.append("WHERE idticket = ?;  ");

		return sql.toString();
	}
}
